package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import services.MinMax;
import services.SelectedList;
import services.Service;

public class ServiceDAO extends DAO<Service>{

	public ServiceDAO(Connection conn) {
		super(conn);
	}

	private void insereStringSimple(Service obj) throws SQLException{
		Statement stmt = this.connection.createStatement();
		String sql = "INSERT INTO SERVICE_CARAC (NameService, NetworkSelected,AutoTemplateSelected,ManualTemplateSelected,AuthenticationSelected,IpAdressSelected,OsSelected,Provider) " +
				"VALUES ("+this.braquet(obj.getName())+" , "+this.braquet(obj.getNetworkSelected())+
				","+this.braquet(obj.getAutoTemplates().getSelected())+","+this.braquet(obj.getManualTemplatesSelected())+
				","+this.braquet(obj.getAuthenticationSelected())+","+this.braquet(obj.getIpAdressSelected())+","+this.braquet(obj.getOsSelected().getSelected())+","+this.braquet(obj.getProvider())+");";
		stmt.executeUpdate(sql);
		stmt.close();
	}

	private void insereSelectedList(Service obj) throws SQLException{
		if(obj.getOsSelected().getTemplate() !=null){
			for(String s :obj.getOsSelected().getTemplate()){
				Statement stmt2 = this.connection.createStatement();
				String sql2 = "INSERT INTO OSTEMPLATE (Name, ServiceID) " +
						"VALUES ("+this.braquet(s)+" , "+this.braquet(obj.getName())+");";
				stmt2.executeUpdate(sql2);
				stmt2.close();
			}
		}
		if(obj.getAutoTemplates().getTemplate() !=null){
			for(String s : obj.getAutoTemplates().getTemplate()){
				Statement stmt3 = this.connection.createStatement();
				String sql3 = "INSERT INTO SERVICE_AUTO_TEMPLATE (Name, ServiceID) " +
						"VALUES ("+this.braquet(s)+" , "+this.braquet(obj.getName())+");";
				stmt3.executeUpdate(sql3);
				stmt3.close();
			}
		}
	}

	private void inserMinMax(Service obj) throws SQLException{
		Statement stmt2 = this.connection.createStatement();
		String sql2 = "INSERT INTO MINMAX (Min, Max, Selected) " +
				"VALUES ("+this.braquet(obj.getHdd().getMin())+" , "+this.braquet(obj.getHdd().getMax())+","+this.braquet(obj.getHdd().getSelected())+");"+
				"INSERT INTO MINMAX (Min, Max, Selected) " +
				"VALUES ("+this.braquet(obj.getRam().getMin())+" , "+this.braquet(obj.getRam().getMax())+","+this.braquet(obj.getRam().getSelected())+");"+
				"INSERT INTO MINMAX (Min, Max, Selected) " +
				"VALUES ("+this.braquet(obj.getSwap().getMin())+" , "+this.braquet(obj.getSwap().getMax())+","+this.braquet(obj.getSwap().getSelected())+");"+
				"INSERT INTO MINMAX (Min, Max, Selected) " +
				"VALUES ("+this.braquet(obj.getCpu().getMin())+" , "+this.braquet(obj.getCpu().getMax())+","+this.braquet(obj.getCpu().getSelected())+");";
		stmt2.executeUpdate(sql2);
		stmt2.close();
	}

	private int getID(MinMax obj) throws SQLException{
		int resultat =0;

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM MINMAX "+
				"WHERE MINMAX.min = "+this.braquet(obj.getMin())+" AND "+
				"MINMAX.Max ="+ this.braquet(obj.getMax())+" AND "+
				"MINMAX.Selected ="+this.braquet(obj.getSelected())+";";
		ResultSet rs = stmt.executeQuery(sql);
		resultat = rs.getInt("ID");
		rs.close();
		stmt.close();
		return resultat;
	}

	private void lierMinMax(Service obj) throws SQLException{
		Statement stmt2 = this.connection.createStatement();
		String sql2 = "UPDATE SERVICE_CARAC SET HDDID="+getID(obj.getHdd())+" ,RAMID="+getID(obj.getRam())+",SWAPID="+getID(obj.getSwap())+",CPUID="+getID(obj.getCpu())+
				" WHERE SERVICE_CARAC.NameService ="+this.braquet(obj.getName())+";";
		stmt2.executeUpdate(sql2);
		stmt2.close();
	}
	/**
	 * Insere un Service dans la base de donnée
	 * @throws SQLException 
	 */
	public boolean create(Service obj) throws SQLException {
		this.connection.setAutoCommit(false);

		//Inserer les string simples
		this.insereStringSimple(obj);

		//inserer les SelectedList
		this.insereSelectedList(obj);

		//inserer les minMax
		this.inserMinMax(obj);

		//referencer les minMax avec l'objet Service
		lierMinMax(obj);

		this.connection.commit();
		this.connection.setAutoCommit(true);

		return true;
	}

	@Override
	public boolean delete(Service obj) {
		// TODO Auto-generated method stub

		return false;
	}

	@Override
	public boolean update(int id,Service obj) {
		// TODO Auto-generated method stub
		return false;
	}

	private MinMax getMinMax(int id) throws SQLException{
		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM MINMAX "+
				"WHERE MINMAX.ID = "+id+";";
		ResultSet rs = stmt.executeQuery(sql);
		MinMax res =new MinMax(rs.getString("Min"), rs.getString("Max"), rs.getString("Selected"));

		rs.close();
		stmt.close();
		return res;
	}

	private ArrayList<String> getListTemplate(String NameService, String nomTable) throws SQLException{
		ArrayList<String> res = new ArrayList<String>();

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM "+nomTable+" WHERE "+nomTable+".ServiceID ="+this.braquet(NameService)+";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			res.add(rs.getString("name"));
		}
		rs.close();
		stmt.close();

		return res;
	}

	public Service find(String nameService) throws SQLException {
		Service s = new Service();
		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SERVICE_CARAC "+
				"WHERE SERVICE_CARAC.NameService = "+this.braquet(nameService)+";";
		ResultSet rs = stmt.executeQuery(sql);

		//recuperer les ID pour les getMin
		int hddID = rs.getInt("HDDID");
		int ramid = rs.getInt("RAMID");
		int swapID = rs.getInt("SWAPID");
		int cpuID = rs.getInt("CPUID");

		//recuperer pour les SelectedList
		String manual = rs.getString("ManualTemplateSelected");
		String os = rs.getString("OsSelected");

		rs.close();
		stmt.close();

		//on refait une requete pour les SelectedList
		SelectedList manualTempList = new SelectedList(manual, getListTemplate(nameService, "SERVICE_AUTO_TEMPLATE"));
		SelectedList osList = new SelectedList(os, getListTemplate(nameService, "OSTEMPLATE"));

		//on refait une requete pour recuperer les MinMax 
		MinMax hdd = getMinMax(hddID);
		MinMax ram = getMinMax(ramid);
		MinMax swap = getMinMax(swapID);
		MinMax cpu= getMinMax(cpuID);
		
		Statement stmt2 = this.connection.createStatement();
		ResultSet rs2 = stmt2.executeQuery(sql);
		//on refait la 1e requete et c bon
		s= new Service(rs2.getString("NameService"), rs2.getString("NetworkSelected"), rs2.getString("AutoTemplateSelected"),
				manualTempList,rs2.getString("AuthenticationSelected"),rs2.getString("IpAdressSelected"),
				osList,hdd ,ram , swap, cpu, rs2.getString("Provider"));

		return s;
	}

	
	public ArrayList<String> getNameServiceFromProvider(String providerName) throws SQLException{
		ArrayList<String> res = new ArrayList<String>();

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SERVICE_CARAC WHERE SERVICE_CARAC.Provider ="+this.braquet(providerName)+";";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			res.add(rs.getString("NameService"));
		}
		rs.close();
		stmt.close();

		return res;
	}
	
	public ArrayList<String> getAllServiceName() throws SQLException{
		ArrayList<String> res = new ArrayList<String>();

		Statement stmt = this.connection.createStatement();
		String sql = "SELECT * FROM SERVICE_CARAC ;";
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			res.add(rs.getString("NameService"));
		}
		rs.close();
		stmt.close();

		return res;
	}
	
	@Override
	public Service find(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

}
